import mysql from 'mysql2/promise';
import { log } from '../utils/logger.js';

// MySQL连接池配置
const pool = mysql.createPool({
  host: process.env.DB_HOST || '58.87.96.234',
  port: process.env.DB_PORT || 3306,
  user: process.env.DB_USER || 'SSH-xue',
  password: process.env.DB_PASSWORD || 'QmbtekEAYpjXMCin',
  database: process.env.DB_NAME || 'SSH-xue',
  charset: process.env.DB_CHARSET || 'utf8mb4',
  timezone: process.env.DB_TIMEZONE || '+08:00',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0
});

// 测试数据库连接
async function testConnection() {
  try {
    const connection = await pool.getConnection();
    log.system('MySQL数据库连接成功', {
      host: process.env.DB_HOST,
      database: process.env.DB_NAME
    });
    connection.release();
    return true;
  } catch (error) {
    log.error('MySQL数据库连接失败', { error: error.message });
    throw error;
  }
}

/**
 * 初始化数据库表结构
 */
export async function initDatabase() {
  try {
    const connection = await pool.getConnection();
    
    try {
      // 用户表
      await connection.execute(`
        CREATE TABLE IF NOT EXISTS users (
          id INT AUTO_INCREMENT PRIMARY KEY,
          username VARCHAR(50) UNIQUE NOT NULL,
          password VARCHAR(255) NOT NULL,
          email VARCHAR(100),
          totp_secret VARCHAR(255),
          totp_enabled TINYINT(1) DEFAULT 0,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          INDEX idx_username (username)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
      `);
      log.system('用户表初始化成功');

      // 用户会话表（用于单用户在线限制）
      await connection.execute(`
        CREATE TABLE IF NOT EXISTS user_sessions (
          id INT AUTO_INCREMENT PRIMARY KEY,
          user_id INT NOT NULL,
          token VARCHAR(500) UNIQUE NOT NULL,
          ip_address VARCHAR(45) NOT NULL,
          user_agent TEXT,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          expires_at TIMESTAMP NOT NULL,
          is_active TINYINT(1) DEFAULT 1,
          FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
          INDEX idx_user_id (user_id),
          INDEX idx_token (token),
          INDEX idx_expires_at (expires_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
      `);
      log.system('会话表初始化成功');

      // SSH服务器配置表
      await connection.execute(`
        CREATE TABLE IF NOT EXISTS servers (
          id INT AUTO_INCREMENT PRIMARY KEY,
          user_id INT NOT NULL,
          name VARCHAR(100) NOT NULL,
          host VARCHAR(255) NOT NULL,
          port INT DEFAULT 22,
          username VARCHAR(50) NOT NULL,
          auth_type VARCHAR(20) DEFAULT 'password',
          password TEXT,
          private_key TEXT,
          group_name VARCHAR(50) DEFAULT 'default',
          sort_order INT DEFAULT 0,
          expire_date DATE,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
          INDEX idx_user_id (user_id),
          INDEX idx_group_name (group_name)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
      `);
      log.system('服务器配置表初始化成功');

      // 访问日志表（包含IP记录）
      await connection.execute(`
        CREATE TABLE IF NOT EXISTS access_logs (
          id INT AUTO_INCREMENT PRIMARY KEY,
          user_id INT,
          ip_address VARCHAR(45) NOT NULL,
          action VARCHAR(100) NOT NULL,
          details TEXT,
          user_agent TEXT,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
          INDEX idx_user_id (user_id),
          INDEX idx_ip_address (ip_address),
          INDEX idx_created_at (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
      `);
      log.system('访问日志表初始化成功');

      // SSH连接日志表
      await connection.execute(`
        CREATE TABLE IF NOT EXISTS ssh_logs (
          id INT AUTO_INCREMENT PRIMARY KEY,
          user_id INT NOT NULL,
          server_id INT NOT NULL,
          ip_address VARCHAR(45) NOT NULL,
          action VARCHAR(100) NOT NULL,
          status VARCHAR(50),
          error_message TEXT,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
          FOREIGN KEY (server_id) REFERENCES servers(id) ON DELETE CASCADE,
          INDEX idx_user_id (user_id),
          INDEX idx_server_id (server_id),
          INDEX idx_created_at (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
      `);
      log.system('SSH日志表初始化成功');

      log.system('MySQL数据库初始化完成');
      return true;
    } finally {
      connection.release();
    }
  } catch (error) {
    log.error('数据库初始化失败', { error: error.message });
    throw error;
  }
}

/**
 * 获取数据库连接
 */
export async function getConnection() {
  return await pool.getConnection();
}

/**
 * 获取数据库连接池
 */
export function getPool() {
  return pool;
}

/**
 * 关闭数据库连接池
 */
export async function closeDB() {
  try {
    await pool.end();
    log.system('MySQL数据库连接池已关闭');
  } catch (error) {
    log.error('关闭数据库连接池失败', { error: error.message });
  }
}

// 优雅关闭
process.on('SIGINT', async () => {
  await closeDB();
  process.exit(0);
});

process.on('SIGTERM', async () => {
  await closeDB();
  process.exit(0);
});

// 导出连接池作为默认导出
export default pool;

// 测试连接
testConnection().catch(err => {
  log.error('数据库初始化测试失败', { error: err.message });
});
